USE master;
GO

IF DATABASEPROPERTYEX ('SlidingWindow', 'Version') > 0
BEGIN
	ALTER DATABASE SlidingWindow SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE SlidingWindow;
END

CREATE DATABASE SlidingWindow ON PRIMARY
  (NAME = SlidingWindow_dat,
  FILENAME = 'C:\MSSQL\Data\SlidingWindow.mdf',
  SIZE = 8MB, FILEGROWTH = 4MB)
  LOG ON
  (NAME = SlidingWindow_log,
  FILENAME = 'C:\MSSQL\Data\SlidingWindow.ldf',
  SIZE = 4MB, MAXSIZE = 4096MB, FILEGROWTH = 2MB);
GO

USE SlidingWindow;
GO

SET NOCOUNT ON;
GO

-- create a partition function as follows:
CREATE PARTITION FUNCTION pfDateRange(INT)
AS RANGE LEFT FOR VALUES(20120701);
GO

-- create the partition scheme as follows:
CREATE PARTITION SCHEME psDateRange
AS PARTITION pfDateRange ALL TO ([PRIMARY]);
GO

/*
We're using the Primary file group for the purposes of this demo
and that I’m referencing my newly created Partition Function.
*/

-- create a very basic fact table
CREATE TABLE dbo.FactTable
(
    ID1       INT NOT NULL,
    ID2       INT NOT NULL,
    FactDate  INT NOT NULL,
    Measure1  MONEY NOT NULL,
    Measure2  INT NOT NULL
)
ON psDateRange(FactDate);
GO

-- create a clustered index on the DateKey 
CREATE CLUSTERED INDEX CX_FactTable_FactDate
	ON dbo.FactTable(FactDate) ON psDateRange(FactDate);
GO

-- create a Staging schema for the staging tables
CREATE SCHEMA Staging;
GO

CREATE TABLE Staging.FactTable_Archive
(
    ID1       INT NOT NULL,
    ID2       INT NOT NULL,
    FactDate  INT NOT NULL,
    Measure1  MONEY NOT NULL,
    Measure2  INT NOT NULL
)
      ON psDateRange(FactDate);
GO
 
CREATE CLUSTERED INDEX CX_FactTable_Archive_FactDate
	ON Staging.FactTable_Archive(FactDate) ON psDateRange(FactDate);
GO

-- and ...
CREATE TABLE Staging.FactTable_Loading
(
    ID1       INT NOT NULL,
    ID2       INT NOT NULL,
    FactDate  INT NOT NULL,
    Measure1  MONEY NOT NULL,
    Measure2  INT NOT NULL
)
ON psDateRange(FactDate);
GO
 
CREATE CLUSTERED INDEX CX_FactTable_Loading_FactDate
	ON Staging.FactTable_Loading(FactDate) ON psDateRange(FactDate);
GO

/*
Again, note that these tables and indexes are identical to our original,
and were both built on the psDateRange Partition Scheme.
This means that they are also partitioned,
though they only contain a single boundary...the most current.
This is because we are actually going to swap the partition in our staging tables
(which really amounts to being the whole table) with the corresponding partition in our fact table.
We will switch in the load staging table with new most recent empty partition,
and we will switch out the oldest partition from the fact table with the empty archiving stage table.
This, in effect, is why the technique is called a “sliding window”...in with the new,
out with the old, all in the same process.
*/

-- now create the stored procedure to load new data
-- into the fact table and move expired data out of it
CREATE PROCEDURE dbo.SlideRangeLeftWindow
      @RetentionDays INT,
      @RunDate DATE = NULL     
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
 
	DECLARE
		@Error                        INT,
		@RowCount                     BIGINT,
		@ErrorLine                    INT,
		@Message                      VARCHAR(255),
		@ExpirationDate               DATE,
		@PartitionBoundaryDate        DATE;
 
	SET @Error = 0;
	
	/*
	Our stored procedure will take two parameters.
	One will help it to identify the current run date,
	and another which will allow it to track the number of days to keep data in the fact table before sending them to be archived.
	Next, we will begin a try-catch block and start it off by checking the contents of the @RunDate parameter.
	This parameter will allow us to manually pass in a specific date to start the load from.
	Otherwise, if it’s NULL, then we’ll just assume that we are only loading data from the previous period.
	We’ll also use the @RunDate parameter in concert with the @RetentionDays parameter to identify the date
	at which the process will need to archive off the expired records.
	*/
	BEGIN TRY
		  IF @RunDate IS NULL
			BEGIN
			-- Use current date if no date specified
				SET @RunDate = CAST(GETDATE() AS DATE);
			END
 
		  -- Set date boundary to latest possible date of previous period
		  SET @RunDate = DATEADD(DAY, -1, @RunDate);
 
		  -- Calculate expiration date based on @RetentionDays and @RunDate
		  SET @ExpirationDate = DATEADD(DAY, @RetentionDays * -1, @RunDate);
		  
		  -- Query partition specific catalog views to determine
		  -- which partition contains our oldest data
		  SELECT
				@PartitionBoundaryDate = 
				CAST(CAST(MIN(prv.value) AS VARCHAR(10)) AS DATE)
		  FROM sys.partition_functions AS pf
				INNER JOIN sys.partition_range_values AS prv
					  ON prv.function_id = pf.function_id
		  WHERE
				pf.name = 'pfDateRange';

	-- Start a new transaction and lock down our destination fact table with an exclusive,
	-- transaction-long table lock
	BEGIN TRAN;
	 -- Acquire exclusive table lock to prevent deadlocking with concurrent activity
	 SELECT TOP 1
		@Error = 0
	 FROM dbo.FactTable WITH (TABLOCKX, HOLDLOCK);

	/*
	Identify the upper and lower boundaries for the date range that we will be loading.
	This is necessary in order to create a check constraint on the data load staging table. The reason for doing this is to ensure that we don’t have any incoming data that falls outside of the range of our current partition. If we attempt to insert new data into a partition of our fact table that already has data in it, resource usage will spike tremendously as the optimizer tries to identify, row by row, where the data should live. For this reason we always want to insert new fact records into an empty partition. And, again, to confirm that we do this, we create the check constraint on the staging table to make darn sure that our date values all fall in the range of this empty partition.
	Since we don’t know what the upper and lower bounds will be before creating the new check constraints, we’ll need to create them using dynamic SQL. No, I’m not usually a big fan of dynamic SQL, but it does have its place. Since we’re not using it for DML, it’s not too much of a problem here. If an error occurs due to there being values outside of the intended range, the catch block will halt further processing and the transaction will roll back making sure that no data was written to the fact table. The life span of this constraint will be very short lived. We only want to issue it to verify our data is in range, and then we’ll remove it.
	Once we’ve identified that we don’t have any dates out of range, the magic of partition switching comes into play. With a simple ALTER TABLE statement, our full load staging table trades places with the appropriate partition in the fact table resulting in a full partition in the fact table and an empty staging table… all in the blink of an eye!
	At this point we can now safely remove the check constraint we added.
	*/
	-- Load new records from the Staging.FactTable_Loading table
	DECLARE @UpperVal       INT;
	DECLARE @UpperBoundID   INT;
	DECLARE @LowerVal       INT;
	DECLARE @AlterSQL       VARCHAR(150);
 
	SELECT
		@UpperVal = CAST(prv.value AS INT),
		@UpperBoundID = prv.boundary_id
	FROM
		sys.partition_functions AS pf
			INNER JOIN sys.partition_range_values AS prv
				ON prv.function_id = pf.function_id
	WHERE
		pf.name = 'pfDateRange'
		AND prv.value =
			(SELECT
				MAX(prv.value)
			FROM
				sys.partition_functions AS pf
					INNER JOIN sys.partition_range_values AS prv
							ON prv.function_id = pf.function_id
			WHERE
				pf.name = 'pfDateRange');
 
	SELECT
		@LowerVal = CAST(prv.value AS INT)
	FROM
		sys.partition_functions AS pf
			INNER JOIN sys.partition_range_values AS prv
					ON prv.function_id = pf.function_id
	WHERE
		pf.name = 'pfDateRange'
		AND boundary_id = @UpperBoundID - 1;
	
	-- Add a check constraint for the FactDate to the Staging.FactTable_Loading table
	IF EXISTS
		(SELECT 1 FROM sys.check_constraints
			WHERE object_id = OBJECT_ID(N'Staging.CK_FactDate')
				AND parent_object_id = OBJECT_ID(N'Staging.FactTable_Loading'))
                       
			ALTER TABLE Staging.FactTable_Loading DROP CONSTRAINT [CK_FactDate];
 
	SET @AlterSQL =
		'ALTER TABLE Staging.FactTable_Loading ADD CONSTRAINT CK_FactDate CHECK (FactDate > ' +
			CAST(@LowerVal AS VARCHAR(8)) + 
			' AND FactDate <= ' + CAST(@UpperVal AS VARCHAR(8)) + ')';
                 
	EXEC(@AlterSQL);
             
	ALTER TABLE Staging.FactTable_Loading SWITCH PARTITION @UpperBoundID
			TO dbo.FactTable PARTITION @UpperBoundID;
                 
	IF  EXISTS
		(SELECT 1 FROM sys.check_constraints
			WHERE object_id = OBJECT_ID(N'Staging.CK_FactDate')
				AND parent_object_id = OBJECT_ID(N'Staging.FactTable_Loading'))
                       
			ALTER TABLE Staging.FactTable_Loading DROP CONSTRAINT CK_FactDate;

	/*
	Initiate some spring cleaning and purge the old, moldy data out of fact table.
	This can mean writing it off to tape, deleting it, or just copying it to a slower drive
	to free up the fast hardware for data that gets queried more often.
	To do this we’ll, first, compare our oldest partition boundary to our calculated expiration date.
	If that boundary is later than our expiration date, we’ll split the partition,
	creating a new one that only contains expired data.
	If the first partition boundary is later than the expiration date,
	split the first partition so that it contains only expired data.
	*/
	IF @PartitionBoundaryDate > @ExpirationDate
		OR @PartitionBoundaryDate IS NULL
		BEGIN
			SET @PartitionBoundaryDate = @ExpirationDate;
                       
			ALTER PARTITION SCHEME psDateRange
				NEXT USED [PRIMARY];
                             
			ALTER PARTITION FUNCTION pfDateRange()
				SPLIT RANGE(CAST(CONVERT(VARCHAR(10), @PartitionBoundaryDate, 112) AS INT));
		END;

	/*
	Now we’ll double check that our archive staging table is empty of all data,
	and then execute our ALTER TABLE statement to switch the oldest partition out with it.
	Follow that step by merging our now empty fact table partition with the next to last partition,
	eliminating the trailing empty partition and the meat of our loading process is accomplished.
	*/
	-- Switch and merge partitions older that retention period
	WHILE @PartitionBoundaryDate <= @ExpirationDate
		BEGIN
			-- Ensure target staging partition is empty
			TRUNCATE TABLE Staging.FactTable_Archive;
                       
			ALTER TABLE dbo.FactTable SWITCH PARTITION 1
				TO Staging.FactTable_Archive PARTITION 1;
                                                           
			-- Purge data permanently
			TRUNCATE TABLE Staging.FactTable_Archive;
                       
			-- Merge first and second partitions
			ALTER PARTITION FUNCTION pfDateRange()
				MERGE RANGE(CAST(CONVERT(VARCHAR(10), @PartitionBoundaryDate, 112) AS INT));
 
			-- Get oldest partition boundary for next iteration
			SET @PartitionBoundaryDate = NULL;
                       
			SELECT
				@PartitionBoundaryDate = CAST(CAST(prv.value AS VARCHAR(8)) AS DATE)
			FROM
				sys.partition_functions AS pf
					INNER JOIN sys.partition_range_values AS prv
						ON prv.function_id = pf.function_id
			WHERE
				pf.name = 'pfDateRange';
		END;

		/*
		Finally, we’ll wrap everything up by taking care of a bit of preemptive safeguarding,
		making sure we have the appropriate empty partitions ready
		for tomorrow’s load as well as completing our TRY block and fleshing out our CATCH block.
		Make sure we have individual partitions for tomorrow and future days.
		*/
		SET @PartitionBoundaryDate = DATEADD(DAY, 1, @RunDate);
			
		WHILE @PartitionBoundaryDate <= DATEADD(DAY, 2, @RunDate)
			BEGIN
				IF NOT EXISTS
					(SELECT
						prv.value
					FROM
						sys.partition_functions AS pf
								INNER JOIN sys.partition_range_values AS prv
									ON prv.function_id = pf.function_id
					WHERE
						pf.name = 'pfDateRange'
						AND CAST(CAST(prv.value AS VARCHAR(8)) AS DATE) = @PartitionBoundaryDate
					)
					BEGIN
						ALTER PARTITION SCHEME psDateRange NEXT USED [PRIMARY];
                                   
						ALTER PARTITION FUNCTION pfDateRange()
							SPLIT RANGE(CAST(CONVERT(VARCHAR(10),@PartitionBoundaryDate, 112) AS INT));
					END;
 
				-- Calculate boundary date for next iteration
				SET @PartitionBoundaryDate = DATEADD(DAY, 1, @PartitionBoundaryDate);
			END
		COMMIT;
	END TRY
	BEGIN CATCH
		SELECT
			@Error = ERROR_NUMBER(),
			@Message = ERROR_MESSAGE(),
			@ErrorLine = ERROR_LINE();
 
			SET @Message =
				'Partition maintenenace failed with error %d at line %d: ' + @Message;
                 
			RAISERROR(@Message, 16, 1, @Error, @ErrorLine) WITH NOWAIT;
 
			IF @@TRANCOUNT > 0
				BEGIN
					ROLLBACK;
				END;
	END CATCH;

	RETURN @Error;
END
GO